package com.css.fxfzypg.modules.rating.task.dao;

import com.css.fxfzypg.modules.rating.task.model.entity.KpRatingTaskIndex3ScoreExpert;
import com.css.fxfzypg.util.PlatformSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

@Repository
public class KpRatingTaskIndex3ScoreExpertRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<KpRatingTaskIndex3ScoreExpert> getByKesdi3Id(String id) {
        return jdbcTemplate.query(String.format("select kp_rating_task_index3_score_expert.* from kp_rating_task_index3_score_expert where kesdi3_id = '%s'", id), new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class));
    }

    public KpRatingTaskIndex3ScoreExpert getByKrtytIdAndExpertIdAndkesdi3Id(String krtytId, String expertId, String kesdi3Id) {
        return jdbcTemplate.queryForObject("SELECT t1.*,t2.score,t2.expert_id, t2.krti3s_id  from kp_rating_task_index3_score t1 LEFT JOIN kp_rating_task_index3_score_expert t2 on t1.id = t2.krti3s_id where t1.kesdi3_id = '" + kesdi3Id + "' and t1.krtyt_id = '" + krtytId + "' and t2.expert_id = '" + expertId + "'", new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class));
    }

    public KpRatingTaskIndex3ScoreExpert getByKrtytyIdAndExpertIdAndKesdi3Id(String krtytId, String expertId, String index3Id) {
        try {
            return jdbcTemplate.queryForObject("select * from kp_rating_task_index3_score_expert where kesdi3_id = ? and krtyt_id = '" + krtytId + "' and expert_id = '" + expertId + "'", new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), index3Id);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public void insert(KpRatingTaskIndex3ScoreExpert expertScore) {
        namedParameterJdbcTemplate.update("INSERT INTO kp_rating_task_index3_score_expert(id, expert_id, score, kesdi3_id, krtyt_id) values(:id, :expertId, :score, :kesdi3Id, :krtytId)", new BeanPropertySqlParameterSource(expertScore));
    }

    public void updateById(KpRatingTaskIndex3ScoreExpert expertScore) {
        namedParameterJdbcTemplate.update("UPDATE kp_rating_task_index3_score_expert SET id=:id, expert_id=:expertId, score=:score, kesdi3_id=:kesdi3Id WHERE id=:id", new BeanPropertySqlParameterSource(expertScore));
    }

    public BigDecimal sumScoreByKesdi3IdAndKrtytId(String kesdi3Id, String krtytId) {
        return jdbcTemplate.queryForObject("SELECT sum(score) from kp_rating_task_index3_score_expert where kesdi3_id = '" + kesdi3Id + "' and krtyt_id = '" + krtytId + "'", BigDecimal.class);
    }

    public KpRatingTaskIndex3ScoreExpert getByKesdi3IdAndExpertIdAndKrtytId(String kesdi3Id, String expertId, String krtytId) {
        try {
            return jdbcTemplate.queryForObject(String.format("select * from kp_rating_task_index3_score_expert where kesdi3_id = '%s' and expert_id = '%s' and krtyt_id = '%s'", kesdi3Id, expertId, krtytId), new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<KpRatingTaskIndex3ScoreExpert> getCommitedDataByKrtytId(String krtytId) {
        return jdbcTemplate.query("SELECT DISTINCT t1.* from kp_rating_task_index3_score_expert t1 LEFT JOIN kp_rating_task_expert_score_progress t2 on t1.krtyt_id =t2.krtyt_id where t2.is_commit = 1 and t1.krtyt_id = ?", new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), krtytId);
    }

    public List<KpRatingTaskIndex3ScoreExpert> getCommitedDataByKesdi3IdAndKrtytyId(String kesdi3Id, String krtytId) {
        return jdbcTemplate.query("SELECT DISTINCT t1.* from kp_rating_task_index3_score_expert t1 LEFT JOIN kp_rating_task_expert_score_progress t2 on t1.krtyt_id =t2.krtyt_id where t2.is_commit = 1 and t1.kesdi3_id = ? and t1.krtyt_id = ?", new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), kesdi3Id, krtytId);
    }

    public List<KpRatingTaskIndex3ScoreExpert> getCommitedDataByKpesdi2IdAndKrtaId(String index2Id, String areaId, String glTaskId) {
        String sql = "SELECT DISTINCT t1.* FROM kp_rating_task_index3_score_expert t1 LEFT JOIN kp_exam_scoring_db_index3 t2 ON t1.kesdi3_id = t2.id LEFT JOIN kp_rating_task_area t3 ON t3.krtyt_id = t1.krtyt_id LEFT JOIN kp_rating_task_expert_score_progress t4 ON t1.krtyt_id = t4.krtyt_id WHERE t3.id = ? AND t2.kpesdi2_id = ? AND t4.krtyt_id = ? and t4.is_commit = 1";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), areaId, index2Id, glTaskId);
    }

    public List<KpRatingTaskIndex3ScoreExpert> getByExpertIdAndKrtytId(String expertId, String krtytId) {
        String sql = "SELECT * from kp_rating_task_index3_score_expert where expert_id = ? and krtyt_id = ? and expert_id='"+ PlatformSessionUtils.getUserId() +"'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), expertId, krtytId);
    }

    public List<KpRatingTaskIndex3ScoreExpert> getByExpertIdAndKrtytIdAndKesdi3Id(String expertId, String krtytId, String index3Id) {
        String sql = "SELECT * from kp_rating_task_index3_score_expert where expert_id = ? and krtyt_id = ? and kesdi3_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), expertId, krtytId, index3Id);
    }

    public List<KpRatingTaskIndex3ScoreExpert> getByExpertIdAndKrtytIdAndKpesdi2IdAndKrtaId(String expertId, String krtytId, String index2Id, String kpRatingTaskAreaId) {
        String sql = "SELECT t1.* FROM kp_rating_task_index3_score_expert t1 LEFT JOIN kp_exam_scoring_db_index3 t2 ON t1.kesdi3_id = t2.ID LEFT JOIN kp_rating_task_area t3 ON t3.krtyt_id = t1.krtyt_id WHERE t3.id = ? AND t2.kpesdi2_id = ? AND t1.expert_id = ? AND t1.krtyt_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), kpRatingTaskAreaId, index2Id, expertId, krtytId);
    }

    public List<KpRatingTaskIndex3ScoreExpert> getByKrtytIdAndKesdi3Id(String krtytId, String index3Id) {
        String sql = "SELECT * from kp_rating_task_index3_score_expert where  krtyt_id = ? and kesdi3_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class),  krtytId, index3Id);

    }

    public BigDecimal getByKrtytId(String krtytId) {
        String sql = "SELECT COALESCE(SUM(score),0) as score FROM kp_rating_task_index3_score_expert where expert_id in (\n" +
                "select krte.expert_id from kp_rating_task_expert_score_progress krtesp LEFT JOIN kp_rating_task_expert krte ON krtesp.krte_id = krte.\"id\" where krtesp.status ='2' or krtesp.status ='3'  and  krtyt_id ='"+krtytId+"' ) and krtyt_id ='"+krtytId+"'";
        return jdbcTemplate.queryForObject(sql,BigDecimal.class);

    }

    public Integer getExportNumByKrtytId(String krtytId) {
        String sql = "SELECT count(DISTINCT expert_id) FROM  kp_rating_task_index3_score_expert where krtyt_id ='"+krtytId+"'";
        return jdbcTemplate.queryForObject(sql,Integer.class);

    }

    public List<KpRatingTaskIndex3ScoreExpert> getByExpertIdAndKrtytIdAvg(String expertId, String krtytId) {
        String sql = "SELECT * from kp_rating_task_index3_score_expert where expert_id = ? and krtyt_id = ? ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), expertId, krtytId);
    }

    public List<KpRatingTaskIndex3ScoreExpert> getKrtytIdAndKpesdi2IdAndKrtaId(String krtytId, String index2Id, String kpRatingTaskAreaId) {
        String sql = "SELECT t1.* FROM kp_rating_task_index3_score_expert t1 LEFT JOIN kp_exam_scoring_db_index3 t2 ON t1.kesdi3_id = t2.ID LEFT JOIN kp_rating_task_area t3 ON t3.krtyt_id = t1.krtyt_id WHERE t3.id = ? AND t2.kpesdi2_id = ? AND t1.krtyt_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(KpRatingTaskIndex3ScoreExpert.class), kpRatingTaskAreaId, index2Id,krtytId);
    }
}
